﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.IO;

namespace EnvCheck.Services
{
    using EnvCheck.Models;
    using System.Windows;

    /// <summary>
    /// 读取全部依赖
    /// </summary>
    public static partial class DatabaseService
    {
        public static List<string> GetAllVersions(out int selected)
        {
            selected = -1;
            var versions = new List<string>();
            using var conn = CreateConn();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT version_name, selected FROM app_version";
            using var reader = cmd.ExecuteReader();
            var id = 0;
            while (reader.Read())
            {
                var name = reader.GetString(0);
                var isSelected = reader.GetBoolean(1);
                if (isSelected)
                    selected = id;
                versions.Add(name);
                id++;
            }
            return versions;
        }


        public static void ChangeSelectedVersion(string versionName)
        {
            using var conn = CreateConn();
            using var transaction = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();

            cmd.CommandText = "UPDATE app_version SET selected=0";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "UPDATE app_version SET selected=1 WHERE version_name=@name";
            cmd.Parameters.AddWithValue("@name", versionName);
            cmd.ExecuteNonQuery();

            transaction.Commit();

        }
#nullable enable
        public static List<AppReqSatisfactionStatus> GetAllRequiredApps(string? targetVersion = null)
        {
            var list = new List<AppReqSatisfactionStatus>();

            string? finalTargetVersion = targetVersion;
            if (finalTargetVersion == null)
            {
                using var versionConn = CreateConn();
                using var versionCmd = versionConn.CreateCommand();
                versionCmd.CommandText = @"
                    SELECT version_name
                    FROM app_version
                    WHERE selected = 1
                ";
                using var versionReader = versionCmd.ExecuteReader();
                if (!versionReader.Read())
                {
                    Serilog.Log.Error("No selected app version found in the database.");
                    return list; // 返回空列表
                }
                finalTargetVersion = versionReader.GetString(0);
                if (finalTargetVersion == null || finalTargetVersion == "")
                {
                    Serilog.Log.Error("Selected app version is empty.");
                    return list;
                }
            }
            using var conn = CreateConn();

            var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                SELECT 
                    d.id,               
                    d.name, 
                    d.expected_version, 
                    d.version_match_strict,
                    a.version_name
                FROM dependencies d
                    INNER JOIN app_version a
                    ON d.app_version_id = a.id
                WHERE d.required = 1
  	                AND a.version_name = @name
                ;
            ";
            cmd.Parameters.AddWithValue("@name", finalTargetVersion);
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Serilog.Log.Information($"Read DB entry: {reader.GetInt32(0)}, {reader.GetString(1)}, {reader.GetString(2)}");
                list.Add(new AppReqSatisfactionStatus
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    ExpectedVersion = reader.GetString(2),
                    //ExpectedVersion = reader.GetString(2)
                });
            }

            return list;
        }
#nullable disable

        public static bool IsVersionAlreadyExisting(string version)
        {
            using var conn = CreateConn();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                SELECT COUNT(*) FROM app_version WHERE version_name = @version_name;
            ";
            cmd.Parameters.AddWithValue("@version_name", version);
            using var reader = cmd.ExecuteReader();
            return reader.Read() && reader.GetInt32(0) > 0;
        }

        public static void DeleteVersionAndRelatedApps(string version)
        {
            if (!IsVersionAlreadyExisting(version))
            {
                MessageBox.Show("内部错误 无效版本");
                return;
            }
            using var conn = CreateConn();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                DELETE FROM app_version WHERE version_name = @version_name;
            ";
            cmd.Parameters.AddWithValue("@version_name", version);
            cmd.ExecuteNonQuery();

            // now that all dependencies are deleted, we can delete the app_version
            cmd.CommandText = @"
                DELETE FROM app_version WHERE id = @id;
            ";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@id", version);
            cmd.ExecuteNonQuery();

        }
        public static void SaveAppReqs(string version, List<AppVersionRequirement> reqs, SqliteConnection connArg = null)
        {
            using var conn = connArg ?? CreateConn();
            conn.Open(); // 确保连接开启

            using var transaction = conn.BeginTransaction();

            try
            {
                using (var versionInsertCmd = conn.CreateCommand())
                {
                    versionInsertCmd.Transaction = transaction;
                    versionInsertCmd.CommandText = @"
                INSERT INTO app_version (version_name, selected)
                VALUES (@version_name, 1);
            ";
                    versionInsertCmd.Parameters.AddWithValue("@version_name", version);
                    versionInsertCmd.ExecuteNonQuery();
                }

                int versionId = -1;
                using (var versionIdReadCmd = conn.CreateCommand())
                {
                    versionIdReadCmd.Transaction = transaction;
                    versionIdReadCmd.CommandText = @"
                SELECT id FROM app_version WHERE version_name = @version_name;
            ";
                    versionIdReadCmd.Parameters.AddWithValue("@version_name", version);

                    using var reader = versionIdReadCmd.ExecuteReader();
                    if (reader.Read())
                    {
                        versionId = reader.GetInt32(0);
                    }
                }

                if (versionId == -1)
                {
                    Serilog.Log.Error($"Failed to retrieve ID for version: {version}");
                    transaction.Rollback();
                    return;
                }

                Serilog.Log.Information($"Retrieved version ID: {versionId} for version: {version}");

                foreach (var req in reqs)
                {
                    if (!req.Required) continue;
                    try
                    {
                        SaveAppReq(versionId, req, conn, transaction); // 传入事务
                    }
                    catch (Exception e)
                    {
                        Serilog.Log.Error($"Failed to save app requirement: {req.Name}, {req.ExpectedVersion}, {req.VersionPolicyValue}");
                        Serilog.Log.Error(e.ToString());
                        transaction.Rollback();
                        return;
                    }
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                Serilog.Log.Error("Transaction failed: " + ex);
                transaction.Rollback();
            }
        }


        public static void SaveAppReq(int versionId, AppVersionRequirement req, SqliteConnection connArg = null, SqliteTransaction transaction = null)
        {
            Serilog.Log.Information($"Saving app requirement: {req.Name}, {req.ExpectedVersion}, {req.VersionPolicyValue}");
            var conn = connArg ?? CreateConn();

            using var cmd = conn.CreateCommand();
            cmd.Transaction = transaction;

            cmd.CommandText = @"
                INSERT INTO dependencies 
                    (name, app_version_id, expected_version, version_match_strict)
                VALUES (@name, @app_version_id, @expected_version, @version_match_strict);
            ";
            cmd.Parameters.AddWithValue("@name", req.Name);
            cmd.Parameters.AddWithValue("@app_version_id", versionId);
            cmd.Parameters.AddWithValue("@expected_version", req.ExpectedVersion);
            cmd.Parameters.AddWithValue("@version_match_strict", (int)req.VersionPolicyValue);

            cmd.ExecuteNonQuery();
        }

    }
}